<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use PHPExcel;
use PHPExcel_IOFactory;
//AP预算模块
class Apyusuan extends Yusuandaoru
{
    
        //审核
    public function shenhe($tid){
        //判断是否为异动
        $tag=Db::table("target_apply")->where("id=$tid")->find();
        Db::startTrans();
        try{
            if($tag["state2"]==2){
                //异动
                Db::table("target_apply")->where(["id"=>$tag["yuanid"]])->update(["tag"=>1]);
            }
            $res=Db::table("target_apply")->where(["id"=>$tid])->update(["state"=>7]);
        }catch(\Exception $e){
            Db::rollBack();
            return 2;
        }
        if($res){
            Db::commit();
            return 1;
        }
        else{
              Db::rollBack();
            return 2;
        }
    }
    //删除
    public function shanchu($tid){
        $tag=Db::table("target_apply")->where("id=$tid")->find();
        Db::startTrans();
        try{
           if($tag["state2"]==2){
                //异动
                Db::table("target_apply")->where(["id"=>$tag["yuanid"]])->update(["tag"=>0,"nextid"=>null]);
            } 
            $res=Db::table("target_apply")->where(["id"=>$tid])->update(["is_deleted"=>1]);
        }catch(\Exception $e){
            Db::rollBack();
            return 2;
        }
        if($res){
            Db::commit();
            return 1;
        }
        else{
              Db::rollBack();
            return 2;
        }
    }
    //提交工作流
    public function tijiao($tid){
        $res=Db::table("target_apply")->where(["id"=>$tid])->update(["state"=>3]);
        if($res)
            return 1;
        else 
            return 2;
    }
        //本部门ap
    public function departap($page=1,$list=10){
        //获取编号
        $typename="AP费用预算格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //获取登录人部门id
        $se_id=Session::get("employee_id");
        $tmp=Db::table("department_employee")->where(["index"=>1,"employee_id"=>$se_id])->find();
        $de_id=$tmp["department_id"];

        //接收数据
        $request = Request::instance();

        
        $data=$request->post();

        $where="t.is_actived=1 and t.is_deleted=0 and t.state<>2  and t.type_id=$type_id and de.index=1 and d.id={$de_id} ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch('departap');
    }
    //所有ap
    public function allap($page=1,$list=10){
        //获取编号
        $typename="AP费用预算格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();
        
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch('allap');
    }
	//Ap预算管理
	public function manage($page=1,$list=10){
		//获取编号
		$typename="AP费用预算格式";
		$tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();
        $user_id=Session::get("login_id");
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id and t.c_users_id=$user_id ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2,t.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch('manage');
		
	}
        //xiugai 
    public function xg_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
       // print_r($data);exit;
           $target=Db::table("target_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=trim($data["title"]);
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
       // $shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];

        if($data["attach"])
            $fujian=$data["attach"];
        else
            $fujian=null;
        $path=null;
         if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/ApYuSuan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/ApYuSuan/'.$savename;

                $res=$this->excel_xiugai("AP费用预算格式",$tid,$path);

                $res=json_decode($res,true);
                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
            $res=["state"=>1,"msg"=>""];
        }
        //return json_encode(["msg"=>"一级渠道不存在或已删除","state"=>2]);
        if($res["state"]==1){
            try{
                if($path){
                    $res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>$state,"path"=>$path]);
                }else{
                     $res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>$state]);
                }
            }catch(\Exception $e){
             return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($state==3){
                $backjson=$this->ystijiao($tid);
                return json_encode($res);
            }else
                return json_encode($res);
        }else{
             return json_encode($res);
        }
            
            
    }
      //异动
    public function zuofei($tid,$from=1){
         $this->assign("tid",$tid);$this->assign("from",$from);
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.shenpiren,t.fujian")
        ->find();
        $this->assign("apply",$apply);
        $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);
            if($apply["state2"]==1)
                $fid=8;
            else
                $fid=9;
          $user_id=Session::get("login_id");
     $rid=Db::Table("users")->alias("u")->join("users_role ur","u.id=ur.users_id")->where("u.id=$user_id")->value("role_id");
         // $rid=2;
          $puser=Db::table("role")->alias("r")
        ->join("role pr","r.parent_id=pr.id")
        ->join("users_role ur","ur.role_id=pr.id")
        ->join("users u","ur.users_id=u.id")
        ->join("employee e","u.employee_id=e.id")
        ->where("r.id={$rid}")->order("u.id")
        ->field("u.id,e.em_name")->select();
        $pguser=Db::table("role")->alias("r")
            ->join("role pr","r.parent_id=pr.id")
            ->join("role_edit re","pr.id=re.role_id")
            ->join("role_group rg","re.group_id=rg.id")
            ->join("users_role_group urg","urg.role_group_id=rg.id")
            ->join("users u","urg.users_id=u.id")
            ->join("employee e","u.employee_id=e.id")
             ->where("r.id={$rid}")->order("u.id")
            ->field("u.id,e.em_name")->select();
          $all_puser=[];$uids=[];
        
        for($i=0;$i<count($puser);$i++){
            $all_puser[]=$puser[$i];
            $uids[]=$puser[$i]["id"];
            
        }
         for($i=0;$i<count($pguser);$i++){
                if(!in_array($pguser[$i]["id"],$uids)){
                    $all_puser[]=$pguser[$j];
                    $uids[]=$pguser[$j]["id"];
                  }  
                
            }
        if(!$all_puser){
          $user=Db::Table("users")->alias("u")->join("employee e","u.employee_id=e.id")->where(["u.id"=>$user_id])->field("u.id,e.em_name")->find();
          $all_puser[0]=$user;
        }
        //print_r($all_puser);
        $this->assign("all_puser",$all_puser);
        //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
    
    //渠道纬度查看
        $qudao=Db::table("target_apply_detail")->query("select c1.title as channel1,sum(feiyong) as channelfei from target_apply_detail as t inner join cg_channel_types1 as c1 on t.channel1=c1.id where t.apply_id=$tid and t.channel1 is not null group by c1.title");
        $sumqudao=0;
        for($i=0;$i<count($qudao);$i++){
            $sumqudao+=$qudao[$i]["channelfei"];
        }
        $this->assign("sumqudao",$sumqudao);
        $this->assign("qudao",$qudao);
        //品牌纬度
        $pinpai=Db::table("target_apply_detail")->query("select pb.brand_name as pinpai,sum(feiyong) as pinpaifei from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id where t.apply_id=$tid and t.pinpai is not null group by t.pinpai,pb.brand_name");
        $sumpinpai=0;
        for($i=0;$i<count($pinpai);$i++){
            $sumpinpai+=$pinpai[$i]["pinpaifei"];
        }
        $this->assign("sumpinpai",$sumpinpai);
        $this->assign("pinpai",$pinpai);
        //经销商和客户
        $jxs=Db::table("target_apply_detail")->query("select j.title as jingxiao,sum(feiyong) as jingxiaofei from target_apply_detail as t inner join cg_jxs as j on t.jingxiao=j.id where t.apply_id=$tid and t.jingxiao is not null  group by t.jingxiao,j.title");
        $sumjingxiao=0;
        for($i=0;$i<count($jxs);$i++){
            $sumjingxiao+=$jxs[$i]["jingxiaofei"];
        }
        $this->assign("sumjingxiao",$sumjingxiao);
        $this->assign("jxs",$jxs);
        //一级费用科目
        $yiji=Db::table("target_apply_detail")->query("select f1.title as yijifeiyong,f1.id as yijiid,sum(feiyong) as yijifei from target_apply_detail as t inner join cg_feiyongkemu1 as f1 on t.yijifeiyong=f1.id where t.apply_id=$tid and t.yijifeiyong is not null group by t.yijifeiyong,f1.title,f1.id");
        $sumyiji=0;
        for($i=0;$i<count($yiji);$i++){
            $sumyiji+=$yiji[$i]["yijifei"];
        }
        $this->assign("yiji",$yiji);
        $this->assign("sumyiji",$sumyiji);
        //二级费用
        $erji=Db::table("target_apply_detail")->query("select t.yijifeiyong,f2.title as erjifeiyong,sum(feiyong) as erjifei from target_apply_detail as t inner join cg_feiyongkemu2 as f2 on t.erjifeiyong=f2.id  where t.apply_id=$tid and t.yijifeiyong is not null and t.erjifeiyong is not null group by t.erjifeiyong,t.yijifeiyong,f2.title");
        $this->assign("erji",$erji);
        $logs=$this->getlog($fid,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        if($apply["state2"]==2){
             $yuandetail=Db::table("target_apply_detail")->alias("t")
            ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
            ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
            ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
            ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
            ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
            ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
            ->join("cg_market_city ac","t.shi=ac.id","LEFT")
            ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("c1.title as channel1,j.title as jingxiao,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
             $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["yijifeiyong"]==$yuandetail[$j]["yijifeiyong"]&&$detail[$i]["erjifeiyong"]==$yuandetail[$j]["erjifeiyong"]&&$detail[$i]["sanjifeiyong"]==$yuandetail[$j]["sanjifeiyong"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["channel1"]==$yuandetail[$j]["channel1"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["fenqu"]==$yuandetail[$j]["fenqu"]&&$detail[$i]["sheng"]==$yuandetail[$j]["sheng"]&&$detail[$i]["shi"]==$yuandetail[$j]["shi"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["yijifeiyong"=>$yuandetail[$i]["yijifeiyong"],"erjifeiyong"=>$yuandetail[$i]["erjifeiyong"],"sanjifeiyong"=>$yuandetail[$i]["sanjifeiyong"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"channel1"=>$yuandetail[$i]["channel1"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"fenqu"=>$yuandetail[$i]["fenqu"],"sheng"=>$yuandetail[$i]["sheng"],"shi"=>$yuandetail[$i]["shi"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
         $btn=$this->getbton($fid,$tid);
        $this->assign("btn",$btn);
        return $this->fetch();
    }
     //异动
    public function xiugai($tid){

        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.shenpiren,t.fujian")
        ->find();
        $this->assign("apply",$apply);
        $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);
          $user_id=Session::get("login_id");
     $rid=Db::Table("users")->alias("u")->join("users_role ur","u.id=ur.users_id")->where("u.id=$user_id")->value("role_id");
         // $rid=2;
          $puser=Db::table("role")->alias("r")
        ->join("role pr","r.parent_id=pr.id")
        ->join("users_role ur","ur.role_id=pr.id")
        ->join("users u","ur.users_id=u.id")
        ->join("employee e","u.employee_id=e.id")
        ->where("r.id={$rid}")->order("u.id")
        ->field("u.id,e.em_name")->select();
        $pguser=Db::table("role")->alias("r")
            ->join("role pr","r.parent_id=pr.id")
            ->join("role_edit re","pr.id=re.role_id")
            ->join("role_group rg","re.group_id=rg.id")
            ->join("users_role_group urg","urg.role_group_id=rg.id")
            ->join("users u","urg.users_id=u.id")
            ->join("employee e","u.employee_id=e.id")
             ->where("r.id={$rid}")->order("u.id")
            ->field("u.id,e.em_name")->select();
          $all_puser=[];$uids=[];
        
        for($i=0;$i<count($puser);$i++){
            $all_puser[]=$puser[$i];
            $uids[]=$puser[$i]["id"];
            
        }
         for($i=0;$i<count($pguser);$i++){
                if(!in_array($pguser[$i]["id"],$uids)){
                    $all_puser[]=$pguser[$j];
                    $uids[]=$pguser[$j]["id"];
                  }  
                
            }
        if(!$all_puser){
          $user=Db::Table("users")->alias("u")->join("employee e","u.employee_id=e.id")->where(["u.id"=>$user_id])->field("u.id,e.em_name")->find();
          $all_puser[0]=$user;
        }
        //print_r($all_puser);
        $this->assign("all_puser",$all_puser);
        //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
    
    //渠道纬度查看
        $qudao=Db::table("target_apply_detail")->query("select c1.title as channel1,sum(feiyong) as channelfei from target_apply_detail as t inner join cg_channel_types1 as c1 on t.channel1=c1.id where t.apply_id=$tid and t.channel1 is not null group by c1.title");
        $sumqudao=0;
        for($i=0;$i<count($qudao);$i++){
            $sumqudao+=$qudao[$i]["channelfei"];
        }
        $this->assign("sumqudao",$sumqudao);
        $this->assign("qudao",$qudao);
        //品牌纬度
        $pinpai=Db::table("target_apply_detail")->query("select pb.brand_name as pinpai,sum(feiyong) as pinpaifei from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id where t.apply_id=$tid and t.pinpai is not null group by t.pinpai,pb.brand_name");
        $sumpinpai=0;
        for($i=0;$i<count($pinpai);$i++){
            $sumpinpai+=$pinpai[$i]["pinpaifei"];
        }
        $this->assign("sumpinpai",$sumpinpai);
        $this->assign("pinpai",$pinpai);
        //经销商和客户
        $jxs=Db::table("target_apply_detail")->query("select j.title as jingxiao,sum(feiyong) as jingxiaofei from target_apply_detail as t inner join cg_jxs as j on t.jingxiao=j.id where t.apply_id=$tid and t.jingxiao is not null  group by t.jingxiao,j.title");
        $sumjingxiao=0;
        for($i=0;$i<count($jxs);$i++){
            $sumjingxiao+=$jxs[$i]["jingxiaofei"];
        }
        $this->assign("sumjingxiao",$sumjingxiao);
        $this->assign("jxs",$jxs);
        //一级费用科目
        $yiji=Db::table("target_apply_detail")->query("select f1.title as yijifeiyong,f1.id as yijiid,sum(feiyong) as yijifei from target_apply_detail as t inner join cg_feiyongkemu1 as f1 on t.yijifeiyong=f1.id where t.apply_id=$tid and t.yijifeiyong is not null group by t.yijifeiyong,f1.title,f1.id");
        $sumyiji=0;
        for($i=0;$i<count($yiji);$i++){
            $sumyiji+=$yiji[$i]["yijifei"];
        }
        $this->assign("yiji",$yiji);
        $this->assign("sumyiji",$sumyiji);
        //二级费用
        $erji=Db::table("target_apply_detail")->query("select t.yijifeiyong,f2.title as erjifeiyong,sum(feiyong) as erjifei from target_apply_detail as t inner join cg_feiyongkemu2 as f2 on t.erjifeiyong=f2.id  where t.apply_id=$tid and t.yijifeiyong is not null and t.erjifeiyong is not null group by t.erjifeiyong,t.yijifeiyong,f2.title");
        $this->assign("erji",$erji);
        return $this->fetch();
    }
     //导出
     public function daochu($tid){
         $apply=Db::table("target_apply")->where(["id"=>$tid])->find();
         $file_path=$apply["path"];$file_name="Ap预算申请-".$apply["title"].".xls";
         header("Content-type:text/html;charset=utf-8"); 
         
           
            if(!file_exists($file_path)){ 
            echo "没有该文件文件"; 
            return ; 
            } 
            $fp=fopen($file_path,"r"); 
            $file_size=filesize($file_path); 
            //下载文件需要用到的头 
            Header("Content-type: application/octet-stream"); 
            Header("Accept-Ranges: bytes"); 
            Header("Accept-Length:".$file_size); 
            Header("Content-Disposition: attachment; filename=".$file_name); 
              ob_clean();
            flush();
            $buffer=1024; 
            $file_count=0; 
            //向浏览器返回数据 
            while(!feof($fp) && $file_count<$file_size){ 
            $file_con=fread($fp,$buffer); 
            $file_count+=$buffer; 
            echo $file_con; 
            } 
            fclose($fp); 
     }
	//新增ap预算
	public function new_ap(){
		return $this->fetch('new_ap');
	}
	//保存预算
	public function new_ap_add(){
		$request = Request::instance();

    	
    	$data=$request->post();
    	$biaoti=trim(htmlspecialchars($data["title"]));
    	if(!$biaoti){
    		 return json_encode(["msg"=>"主题不能为空","state"=>2]);
    	}
    	$remark=trim(htmlspecialchars($data["remark"]));
    	$state=trim($data["type"]);
        $fujian=null;
        if($data["attach"])
            $fujian=$data["attach"];
    	$file = request()->file('wenjian');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/ApYuSuan' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $path=ROOT_PATH . 'public/uploads/ApYuSuan/'.$savename;
	          	$res=$this->excel_duqu("AP费用预算格式",$state,$biaoti,$remark,$path,$fujian);
	          	return $res;

	          	
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
    	
    	//echo 1;exit;
		//print_r($data);exit;
	}

	

     //异动预算
    public function yd_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
        //print_r($data);exit;
        $target=Db::table("target_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=$target["title"];
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
       // $shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];
        if($data["attach"])
            $fujian=$data["attach"];
        else
            $fujian=null;
        if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/ApYuSuan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/ApYuSuan/'.$savename;
                $res=$this->excel_yidong("AP费用预算格式",$tid,$state,$biaoti,$remark,$path,$fujian);
                return $res;

                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
             return json_encode(["msg"=>"文件不能为空","state"=>2]);
        }

    }
      //异动
    public function yidong($tid){

        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.shenpiren")
        ->find();
        $this->assign("apply",$apply);
          $user_id=Session::get("login_id");
     $rid=Db::Table("users")->alias("u")->join("users_role ur","u.id=ur.users_id")->where("u.id=$user_id")->value("role_id");
         // $rid=2;
          $puser=Db::table("role")->alias("r")
        ->join("role pr","r.parent_id=pr.id")
        ->join("users_role ur","ur.role_id=pr.id")
        ->join("users u","ur.users_id=u.id")
        ->join("employee e","u.employee_id=e.id")
        ->where("r.id={$rid}")->order("u.id")
        ->field("u.id,e.em_name")->select();
        $pguser=Db::table("role")->alias("r")
            ->join("role pr","r.parent_id=pr.id")
            ->join("role_edit re","pr.id=re.role_id")
            ->join("role_group rg","re.group_id=rg.id")
            ->join("users_role_group urg","urg.role_group_id=rg.id")
            ->join("users u","urg.users_id=u.id")
            ->join("employee e","u.employee_id=e.id")
             ->where("r.id={$rid}")->order("u.id")
            ->field("u.id,e.em_name")->select();
          $all_puser=[];$uids=[];
        
        for($i=0;$i<count($puser);$i++){
            $all_puser[]=$puser[$i];
            $uids[]=$puser[$i]["id"];
            
        }
         for($i=0;$i<count($pguser);$i++){
                if(!in_array($pguser[$i]["id"],$uids)){
                    $all_puser[]=$pguser[$j];
                    $uids[]=$pguser[$j]["id"];
                  }  
                
            }
        if(!$all_puser){
          $user=Db::Table("users")->alias("u")->join("employee e","u.employee_id=e.id")->where(["u.id"=>$user_id])->field("u.id,e.em_name")->find();
          $all_puser[0]=$user;
        }
        //print_r($all_puser);
        $this->assign("all_puser",$all_puser);
        //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
    
    //渠道纬度查看
        $qudao=Db::table("target_apply_detail")->query("select c1.title as channel1,sum(feiyong) as channelfei from target_apply_detail as t inner join cg_channel_types1 as c1 on t.channel1=c1.id where t.apply_id=$tid and t.channel1 is not null group by c1.title");
        $sumqudao=0;
        for($i=0;$i<count($qudao);$i++){
            $sumqudao+=$qudao[$i]["channelfei"];
        }
        $this->assign("sumqudao",$sumqudao);
        $this->assign("qudao",$qudao);
        //品牌纬度
        $pinpai=Db::table("target_apply_detail")->query("select pb.brand_name as pinpai,sum(feiyong) as pinpaifei from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id where t.apply_id=$tid and t.pinpai is not null group by t.pinpai,pb.brand_name");
        $sumpinpai=0;
        for($i=0;$i<count($pinpai);$i++){
            $sumpinpai+=$pinpai[$i]["pinpaifei"];
        }
        $this->assign("sumpinpai",$sumpinpai);
        $this->assign("pinpai",$pinpai);
        //经销商和客户
        $jxs=Db::table("target_apply_detail")->query("select j.title as jingxiao,sum(feiyong) as jingxiaofei from target_apply_detail as t inner join cg_jxs as j on t.jingxiao=j.id where t.apply_id=$tid and t.jingxiao is not null  group by t.jingxiao,j.title");
        $sumjingxiao=0;
        for($i=0;$i<count($jxs);$i++){
            $sumjingxiao+=$jxs[$i]["jingxiaofei"];
        }
        $this->assign("sumjingxiao",$sumjingxiao);
        $this->assign("jxs",$jxs);
        //一级费用科目
        $yiji=Db::table("target_apply_detail")->query("select f1.title as yijifeiyong,f1.id as yijiid,sum(feiyong) as yijifei from target_apply_detail as t inner join cg_feiyongkemu1 as f1 on t.yijifeiyong=f1.id where t.apply_id=$tid and t.yijifeiyong is not null group by t.yijifeiyong,f1.title,f1.id");
        $sumyiji=0;
        for($i=0;$i<count($yiji);$i++){
            $sumyiji+=$yiji[$i]["yijifei"];
        }
        $this->assign("yiji",$yiji);
        $this->assign("sumyiji",$sumyiji);
        //二级费用
        $erji=Db::table("target_apply_detail")->query("select t.yijifeiyong,f2.title as erjifeiyong,sum(feiyong) as erjifei from target_apply_detail as t inner join cg_feiyongkemu2 as f2 on t.erjifeiyong=f2.id  where t.apply_id=$tid and t.yijifeiyong is not null and t.erjifeiyong is not null group by t.erjifeiyong,t.yijifeiyong,f2.title");
        $this->assign("erji",$erji);
         if($apply["state2"]==1)
            $logs=$this->getlog(8,$tid);
        else
             $logs=$this->getlog(9,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        return $this->fetch();
    }
	//预算明细
	public function detail($tid){

		$apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.fujian")
        ->find();
        $this->assign("apply",$apply);
         $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);
        //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
		//渠道纬度查看
		$qudao=Db::table("target_apply_detail")->query("select c1.title as channel1,sum(feiyong) as channelfei from target_apply_detail as t inner join cg_channel_types1 as c1 on t.channel1=c1.id where t.apply_id=$tid and t.channel1 is not null group by c1.title");
		$sumqudao=0;
		for($i=0;$i<count($qudao);$i++){
			$sumqudao+=$qudao[$i]["channelfei"];
		}
		$this->assign("sumqudao",$sumqudao);
		$this->assign("qudao",$qudao);
		//品牌纬度
		$pinpai=Db::table("target_apply_detail")->query("select pb.brand_name as pinpai,sum(feiyong) as pinpaifei from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id where t.apply_id=$tid and t.pinpai is not null group by t.pinpai,pb.brand_name");
		$sumpinpai=0;
		for($i=0;$i<count($pinpai);$i++){
			$sumpinpai+=$pinpai[$i]["pinpaifei"];
		}
		$this->assign("sumpinpai",$sumpinpai);
		$this->assign("pinpai",$pinpai);
		//经销商和客户
		$jxs=Db::table("target_apply_detail")->query("select j.title as jingxiao,sum(feiyong) as jingxiaofei from target_apply_detail as t inner join cg_jxs as j on t.jingxiao=j.id where t.apply_id=$tid and t.jingxiao is not null  group by t.jingxiao,j.title");
		$sumjingxiao=0;
		for($i=0;$i<count($jxs);$i++){
			$sumjingxiao+=$jxs[$i]["jingxiaofei"];
		}
		$this->assign("sumjingxiao",$sumjingxiao);
		$this->assign("jxs",$jxs);
		//一级费用科目
		$yiji=Db::table("target_apply_detail")->query("select f1.title as yijifeiyong,f1.id as yijiid,sum(feiyong) as yijifei from target_apply_detail as t inner join cg_feiyongkemu1 as f1 on t.yijifeiyong=f1.id where t.apply_id=$tid and t.yijifeiyong is not null group by t.yijifeiyong,f1.title,f1.id");
		$sumyiji=0;
		for($i=0;$i<count($yiji);$i++){
			$sumyiji+=$yiji[$i]["yijifei"];
		}
		$this->assign("yiji",$yiji);
		$this->assign("sumyiji",$sumyiji);
		//二级费用
		$erji=Db::table("target_apply_detail")->query("select t.yijifeiyong,f2.title as erjifeiyong,sum(feiyong) as erjifei from target_apply_detail as t inner join cg_feiyongkemu2 as f2 on t.erjifeiyong=f2.id  where t.apply_id=$tid and t.yijifeiyong is not null and t.erjifeiyong is not null group by t.erjifeiyong,t.yijifeiyong,f2.title");
		$this->assign("erji",$erji);
        if($apply["state2"]==1)
            $logs=$this->getlog(8,$tid);
        else
             $logs=$this->getlog(9,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        if($apply["state2"]==2){
             $yuandetail=Db::table("target_apply_detail")->alias("t")
            ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
            ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
            ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
            ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
            ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
            ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
            ->join("cg_market_city ac","t.shi=ac.id","LEFT")
            ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("c1.title as channel1,j.title as jingxiao,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
             $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["yijifeiyong"]==$yuandetail[$j]["yijifeiyong"]&&$detail[$i]["erjifeiyong"]==$yuandetail[$j]["erjifeiyong"]&&$detail[$i]["sanjifeiyong"]==$yuandetail[$j]["sanjifeiyong"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["channel1"]==$yuandetail[$j]["channel1"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["fenqu"]==$yuandetail[$j]["fenqu"]&&$detail[$i]["sheng"]==$yuandetail[$j]["sheng"]&&$detail[$i]["shi"]==$yuandetail[$j]["shi"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["yijifeiyong"=>$yuandetail[$i]["yijifeiyong"],"erjifeiyong"=>$yuandetail[$i]["erjifeiyong"],"sanjifeiyong"=>$yuandetail[$i]["sanjifeiyong"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"channel1"=>$yuandetail[$i]["channel1"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"fenqu"=>$yuandetail[$i]["fenqu"],"sheng"=>$yuandetail[$i]["sheng"],"shi"=>$yuandetail[$i]["shi"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
		return $this->fetch("detail");
	}
    //预算统计查询
    public function tongji($page=1,$list=20){
        $typename="AP费用预算格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();
        $where="t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.type_id=$type_id and t.tag<>1 ";
        if($data){
            if(trim($data["cainian"]))
                $where.="and ap.cainian='{$data["cainian"]}' ";
           if(trim($data["yijikaoheqi"]))
                $where.="and ap.yijikaoheqi='{$data["yijikaoheqi"]}' ";
            if(trim($data["erjikaoheqi"]))
                $where.="and ap.erjikaoheqi='{$data["erjikaoheqi"]}' ";
            if(trim($data["pinpai"]))
                $where.="and ap.pinpai='{$data["pinpai"]}' ";
            if(trim($data["yijifeiyong"]))
                $where.="and ap.yijifeiyong='{$data["yijifeiyong"]}' ";
            if(trim($data["erjifeiyong"]))
                $where.="and ap.erjifeiyong='{$data["erjifeiyong"]}' ";
             if(trim($data["channel1"]))
                $where.="and ap.channel1='{$data["channel1"]}' ";
             if(trim($data["fenqu"]))
                $where.="and z.id='{$data["fenqu"]}' ";
            if(trim($data["sheng"]))
                $where.="and p.id='{$data["sheng"]}' ";
            if(trim($data["shi"])){
                $where.="and c.id='{$data["shi"]}' ";
            }
            if(trim($data["jingxiao"])){
                $where.="and ap.jingxiao='{$data["jingxiao"]}' ";
            }
        }else{
            $data=["cainian"=>"","yijikaoheqi"=>"","erjikaoheqi"=>"","pinpai"=>"","yijifeiyong"=>""
                    ,"erjifeiyong"=>"","channel1"=>"","fenqu"=>"","sheng"=>"","shi"=>"","jingxiao"=>""
            ];
        }
        //echo $where;
        $sum=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.title","LEFT")
        ->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
        ->where($where)
        ->count();

        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
        ->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
         ->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","ap.channel2=c2.id","LEFT")
        
        ->join("cg_system x","ap.xitong=x.id","LEFT")
        ->join("cg_net n","ap.wangdian=n.id","LEFT")
        ->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
        ->join("pro_category pc","ap.pinlei=pc.id","LEFT")
        ->join("pro_series ps","ap.xilie=ps.id","LEFT")
        ->join("cg_feiyongkemu1 f1","ap.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","ap.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","ap.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","ap.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","ap.erjikaoheqi=k2.id","LEFT")
        ->where($where)
        ->order("t.id desc,ap.id desc")
        ->limit($start,$list)
        ->field("c1.title as channel1,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jingxiao,pb.brand_name as pinpai,f1.title as  yijifeiyong,f2.title as erjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,ap.feiyong")
        ->select();
        //print_r($apply);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        //所有财年
        $cainian=Db::table("cg_cainian")->select();
        $this->assign("cainian",$cainian);
        //一级考核期
        $kaohe1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.title,k1.id")->select();
        $this->assign("kaohe1",$kaohe1);
        $jsonkaohe1=json_encode($kaohe1);
        $this->assign("jsonkaohe1",$jsonkaohe1);
         $kaohe2=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.id as yijikaoheqi,k2.title,k2.id")->select();
          $this->assign("kaohe2",$kaohe2);
           $jsonkaohe2=json_encode($kaohe2);
          $this->assign("jsonkaohe2",$jsonkaohe2);
          //print_r($kaohe2);
        //品牌
        $pinpai=Db::table("pro_brands")->where(["is_deleted"=>0])->select();
        $this->assign("pinpai",$pinpai);
        //一级费用科目
        $yijifeiyong=Db::table("cg_feiyongkemu1")->select();
        $this->assign("yijifeiyong",$yijifeiyong);

        //二级费用科目
        $erjifeiyong=Db::table("cg_feiyongkemu2")->alias("f2")
        ->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
        ->field("f1.id as yijifeiyong,f2.title,f2.id")
        ->select();
        $this->assign("erjifeiyong",$erjifeiyong);
         $jsonerjifeiyong=json_encode($erjifeiyong);
        $this->assign("jsonerjifeiyong",$jsonerjifeiyong);
        //渠道
        $qudao=Db::table("cg_channel_types1")->select();
        $this->assign("qudao",$qudao);
        //分区
        $fenqu=Db::table("cg_market_zoning")->where(["isdel"=>0])->select();
        $this->assign("fenqu",$fenqu);
        //省份
        $sheng=Db::table("cg_market_provice")->alias("p")
        ->join("cg_market_zoning z","p.zoning=z.id")
        ->field("z.id as fenqu,p.title,p.id")->select();
        $this->assign("sheng",$sheng);
       
         $jsonsheng=json_encode($sheng);
          $this->assign("jsonsheng",$jsonsheng);
        //市
        $shi=Db::table("cg_market_city")->alias("c")
        ->join("cg_market_provice p","c.provice=p.id")
        ->field("p.id as sheng,c.title,c.id")->select();
        $this->assign("shi",$shi);
        $jsonshi=json_encode($shi);
          $this->assign("jsonshi",$jsonshi);
        //经销商
        $jingxiao=Db::table("cg_jxs")->alias("j")
        ->join("cg_market_city c","j.city=c.id","LEFT")->where(["j.isdel"=>0])
        ->field("c.id as shi,j.title,j.id")->select();
        $this->assign("jingxiao",$jingxiao);
        $jsonjingxiao=json_encode($jingxiao);
          $this->assign("jsonjingxiao",$jsonjingxiao);
        return $this->fetch("tongji");
    }
}